﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using System.Configuration;
using Sinacor.Coletor.WindowsService.Host;

namespace Sinacor.Coletor.WindowsService
{
    public class Mensagem
    {
        public List<HostRequest> LerMensagem()
        {
            List<HostRequest> listaRetorno = new List<HostRequest>();
            OracleConnection oracleDataBase = new OracleConnection(ConfigurationSettings.AppSettings["CONNECTION"].ToString());
            oracleDataBase.Open();

            using (OracleCommand cmd = new OracleCommand())
            {
                cmd.Connection = oracleDataBase;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "POR_CARREGA_OFERTA_BROKER.POR_CARGA";
                cmd.Parameters.Add("V_SNADA", OracleType.VarChar).Value = "X";
                cmd.ExecuteNonQuery();
            }

            string sqlCommand = @"SELECT 
                                    'TORSINL_OFER_BROKER' NM_TABELA,
                                    DT_DATMOV, 
                                    DT_SISTEMA, 
                                    NR_SEQ, 
                                    CD_CLIENTE_BRO, 
                                    CD_CLIENTE, 
                                    DS_SINAL,                                    
                                    NULL CD_SISTEMA,
                                    NULL DT_TIMESTAMP
                                FROM 
                                    TORSINL_OFER_BROKER
                                WHERE 
                                    (CD_CLIENTE_BRO IN ({0})) AND
                                    (COD_EMP IS NULL OR COD_EMP = :CD_EMP) AND
                                    (COD_ESTD_SINAL IS NULL OR COD_ESTD_SINAL = 'A')
                                UNION
                                SELECT 
                                    'TORHUB_TXT_BROKER' NM_TABELA, 
                                    DT_DATMOV, 
                                    NULL, 
                                    NR_SEQ, 
                                    CD_CLIENTE_BRO, 
                                    CD_CLIENTE, 
                                    DS_TXT DS_SINAL, 
                                    CD_SISTEMA,  
                                    DT_TIMESTAMP
                                FROM 
                                    TORHUB_TXT_BROKER
                                WHERE 
                                    (CD_CLIENTE_BRO IN ({0})) AND
                                    (COD_EMP IS NULL OR COD_EMP = :CD_EMP) AND
                                    (COD_ESTD_SINAL IS NULL OR COD_ESTD_SINAL = 'A')
                                ";

            sqlCommand = string.Format(sqlCommand, ConfigurationSettings.AppSettings["ID_BROKER"].ToString());
            using (OracleCommand cmd = new OracleCommand(sqlCommand, oracleDataBase))
            {
                //cmd.Parameters.Add("CD_CLIENTE_BRO", OracleType.Number).Value = ConfigurationSettings.AppSettings["ID_BROKER"].ToString();
                cmd.Parameters.Add("CD_EMP", OracleType.Number).Value = ConfigurationSettings.AppSettings["ID_MEMBER"].ToString();

                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        HostRequest item = new HostRequest() {
                            DataMovimento = DateTime.Parse(reader["DT_DATMOV"].ToString()),
                            DataSistema = reader["DT_SISTEMA"].ToString() != string.Empty ? DateTime.Parse(reader["DT_SISTEMA"].ToString()) : DateTime.Now,
                            NumeroSequencia = Int32.Parse(reader["NR_SEQ"].ToString()),
                            CodigoCliente = Int32.Parse(reader["CD_CLIENTE"].ToString()),
                            DescricaoSinal = reader["DS_SINAL"].ToString(),
                            Status = Status.AguardandoEnvio,
                            CodigoSistema = reader["CD_SISTEMA"].ToString(),
                            Tabela = reader["NM_TABELA"].ToString() == "TORSINL_OFER_BROKER" ? Tabela.torsinl_ofer : Tabela.torhub_txt,
                            DataTimeStamp = reader["DT_TIMESTAMP"].ToString() != string.Empty ? DateTime.Parse(reader["DT_TIMESTAMP"].ToString()) : DateTime.Now
                        };

                        listaRetorno.Add(item);
                    }
                }
            }
            oracleDataBase.Close();
            return listaRetorno;
        }

        internal bool GravarMensagem(List<HostRequest> listaRetorno)
        {
            OracleConnection oracleDataBase = new OracleConnection(ConfigurationSettings.AppSettings["CONNECTION"].ToString());
            try
            {
                oracleDataBase.Open();

                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = oracleDataBase;
                    
                    foreach (HostRequest item in listaRetorno)
                    {
                        if (item.Status == Status.Processado)
                        {
                            cmd.CommandText = string.Format("UPDATE {0} SET COD_ESTD_SINAL='E', TMST_ATLZ=SYSDATE WHERE NR_SEQ=:NR_SEQ AND DT_DATMOV=:DT_DATMOV", item.Tabela == Tabela.torsinl_ofer ? "TORSINL_OFER_BROKER" : "TORHUB_TXT_BROKER");
                            cmd.Parameters.Add("NR_SEQ", OracleType.Number).Value = item.NumeroSequencia;
                            cmd.Parameters.Add("DT_DATMOV", OracleType.DateTime).Value = item.DataMovimento;
                            cmd.ExecuteNonQuery();
                        }
                        else
                            return false;
                    }
                }
                return true;
            }
            finally
            {
                oracleDataBase.Close();
            }
        }
    }
}
